/*
Run this script on:

        mssql-ct.db.jwan.it,1433.Caronte2014    -  This database will be modified

to synchronize it with:

        kalgan01.cs.jwan.it,1443.Jambo2014_sviluppo

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 10/04/2014 16:02:29

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping foreign keys from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP
CONSTRAINT [FK_jb_categoSconti_catego1],
CONSTRAINT [FK_jb_categoSconti_sconti],
CONSTRAINT [FK_jb_categoSconti_catego2],
CONSTRAINT [FK_jb_categoSconti_jb_tessere_tipo]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping foreign keys from [dbo].[jb_voucher_dett]'
GO
ALTER TABLE [dbo].[jb_voucher_dett] DROP
CONSTRAINT [FK_jb_voucher_dett_jb_categoSconti]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [PK_jb_configSconti]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__GRUPP__611DF409]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__QUOTA__62121842]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__QUOTA__63063C7B]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__QUOTA__63FA60B4]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__VOUCH__64EE84ED]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__OMAGG__65E2A926]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__CUMUL__66D6CD5F]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__FORZA__67CAF198]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__CTRLT__68BF15D1]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__TIPO___69B33A0A]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__ORDIN__6AA75E43]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] DROP CONSTRAINT [DF__tmp_rg_xx__IS_PA__6B9B827C]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Rebuilding [dbo].[jb_categoSconti]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_jb_categoSconti]
(
[CATKEY] [int] NOT NULL,
[CODSCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DESCRIZIONE] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[GRUPPO] [bit] NOT NULL DEFAULT ((0)),
[QUOTA_GUIDA] [int] NOT NULL DEFAULT ((0)),
[SCONTO_GUIDA] [decimal] (18, 2) NOT NULL DEFAULT ((0)),
[QUOTA_PAX] [int] NOT NULL DEFAULT ((0)),
[CATKEY_PAX] [int] NULL,
[VOUCHER] [bit] NOT NULL DEFAULT ((0)),
[OMAGGIO] [bit] NOT NULL DEFAULT ((0)),
[CTRLTKT] [bit] NOT NULL DEFAULT ((0)),
[TIPO_TESSERA] [int] NULL DEFAULT ((0)),
[ORDINE] [int] NOT NULL DEFAULT ((0)),
[IS_PASSAGGIO_TESSERA] [bit] NOT NULL DEFAULT ((0)),
[RESIDENTE] [bit] NOT NULL DEFAULT ((0))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_rg_xx_jb_categoSconti]([CATKEY], [CODSCO], [DESCRIZIONE], [GRUPPO], [QUOTA_GUIDA], [QUOTA_PAX], [CATKEY_PAX], [VOUCHER], [OMAGGIO], [CTRLTKT], [TIPO_TESSERA], [ORDINE]) SELECT [CATKEY], [CODSCO], [DESCRIZIONE], [GRUPPO], [QUOTA_GUIDA], [QUOTA_PAX], [CATKEY_PAX], [VOUCHER], [OMAGGIO], [CTRLTKT], [TIPO_TESSERA], [ORDINE] FROM [dbo].[jb_categoSconti]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[jb_categoSconti]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_jb_categoSconti]', N'jb_categoSconti'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_jb_configSconti] on [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] ADD CONSTRAINT [PK_jb_configSconti] PRIMARY KEY CLUSTERED  ([CATKEY], [CODSCO])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [dbo].[jb_abbonamenti]'
GO
ALTER TABLE [dbo].[jb_abbonamenti] ADD CONSTRAINT [CK_jb_abbonamenti] CHECK (([dbo].[fn_jb_check_abbonamenti_validi]([ID_TESSERA],[DATA_INIZIO])>(0)))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_categoSconti]'
GO
ALTER TABLE [dbo].[jb_categoSconti] ADD
CONSTRAINT [FK_jb_categoSconti_catego1] FOREIGN KEY ([CATKEY]) REFERENCES [dbo].[catego] ([CATKEY]),
CONSTRAINT [FK_jb_categoSconti_sconti] FOREIGN KEY ([CODSCO]) REFERENCES [dbo].[sconti] ([CODSCO]),
CONSTRAINT [FK_jb_categoSconti_catego2] FOREIGN KEY ([CATKEY_PAX]) REFERENCES [dbo].[catego] ([CATKEY]),
CONSTRAINT [FK_jb_categoSconti_jb_tessere_tipo] FOREIGN KEY ([TIPO_TESSERA]) REFERENCES [dbo].[jb_tessere_tipo] ([TIPO_TESSERA])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_voucher_dett]'
GO
ALTER TABLE [dbo].[jb_voucher_dett] ADD
CONSTRAINT [FK_jb_voucher_dett_jb_categoSconti] FOREIGN KEY ([CATKEY], [CODSCO]) REFERENCES [dbo].[jb_categoSconti] ([CATKEY], [CODSCO])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Controlla che non ci siano rinnovi sovrapposti', 'SCHEMA', N'dbo', 'TABLE', N'jb_abbonamenti', 'CONSTRAINT', N'CK_jb_abbonamenti'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_addextendedproperty N'MS_Description', N'associa l''eventuale categoria pax ad un''altra categoria (es. auto con autista)', 'SCHEMA', N'dbo', 'TABLE', N'jb_categoSconti', 'COLUMN', N'CATKEY_PAX'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering permissions on [dbo].[jb_categoSconti]'
GO
GRANT SELECT ON  [dbo].[jb_categoSconti] TO [JamboClient]
GRANT SELECT ON  [dbo].[jb_categoSconti] TO [JamboGest]
GRANT INSERT ON  [dbo].[jb_categoSconti] TO [JamboGest]
GRANT DELETE ON  [dbo].[jb_categoSconti] TO [JamboGest]
GRANT UPDATE ON  [dbo].[jb_categoSconti] TO [JamboGest]
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
